package cn.bone.excel;

import com.alibaba.fastjson.JSON;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelMain {

    public static void main(String... args) throws Exception {

        List<NameData> list0 = readSheet0();
        List<NameData> list1 = readSheet1();
        List<NameData> list2 = readSheet2();
        List<NameData> list3 = readSheet3();

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet0 = workbook.createSheet("总表");
        createSheet(sheet0, list0);
        XSSFSheet sheet1 = workbook.createSheet("普通类");
        createSheet(sheet1, list1);
        XSSFSheet sheet2 = workbook.createSheet("国家专项");
        createSheet(sheet2, list2);
        XSSFSheet sheet3 = workbook.createSheet("地方专项");
        createSheet(sheet3, list3);

        String outName = "e:/tmp/excel总表.xlsx";
        FileOutputStream outputStream = new FileOutputStream(outName);
        workbook.write(outputStream);
    }


    public static List<NameData> readSheet0() throws Exception{
        String filePath = "e:/tmp/2021.xlsx";
        File file = new File(filePath);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(0);
        int rowCount = sheet.getPhysicalNumberOfRows();
        System.out.println(rowCount);

        List<NameData> list = new ArrayList<>();
        Map<String, NameData> map = new HashMap<>();

        for(int i=1; i<rowCount; i++){
            Row row = sheet.getRow(i);
            Cell cell1 = row.getCell(1);
            String name1 = cell1.getStringCellValue();
            Cell cell2 = row.getCell(2);
            String name2 = cell2.getStringCellValue();
            Cell cell3 = row.getCell(3);
            String name3 = cell3.getStringCellValue();
            Cell cell7 = row.getCell(7);
            String name7 = cell7.getStringCellValue();

            //System.out.println(i+"\t"+name1+"\t"+name2+"\t"+name3+"\t"+name7);

            if(!map.containsKey(name2)){
                NameData data = new NameData();
                data.setName1(name1);
                data.setName2(name2);
                data.setNameCode(name3);
                data.setNameValue(name7);
                map.put(name2, data);
                list.add(data);
            }else{
                NameData data = map.get(name2);
                String value = data.getNameValue() + "、" + name7;
                data.setNameValue(value);
            }
        }
        return list;
    }

    public static List<NameData> readSheet1() throws Exception {
        String filePath = "e:/tmp/2021.xlsx";
        File file = new File(filePath);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(1);
        int rowCount = sheet.getPhysicalNumberOfRows();
        List<NameData> list = new ArrayList<>();
        Map<String, NameData> map = new HashMap<>();
        for(int i=1; i<rowCount; i++){
            Row row = sheet.getRow(i);
            String nameCode = row.getCell(0).getStringCellValue();
            String name1 = row.getCell(1).getStringCellValue();
            String name2 = row.getCell(2).getStringCellValue();
            String nameValue = row.getCell(7).getStringCellValue();
            if(!map.containsKey(name2)){
                NameData data = new NameData();
                data.setName1(name1);
                data.setName2(name2);
                data.setNameCode(nameCode);
                data.setNameValue(nameValue);
                map.put(name2, data);
                list.add(data);
            }else{
                NameData data = map.get(name2);
                String value = data.getNameValue() + "、" + nameValue;
                data.setNameValue(value);
            }
        }
        return list;
    }

    public static List<NameData> readSheet2() throws Exception {

        String filePath = "e:/tmp/2021.xlsx";
        File file = new File(filePath);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(2);
        int rowCount = sheet.getPhysicalNumberOfRows();
        List<NameData> list = new ArrayList<>();
        Map<String, NameData> map = new HashMap<>();
        for(int i=1; i<rowCount; i++){
            Row row = sheet.getRow(i);
            String nameCode = row.getCell(0).getStringCellValue();
            String name1 = row.getCell(1).getStringCellValue();
            String name2 = row.getCell(2).getStringCellValue();
            String nameValue = row.getCell(6).getStringCellValue();
            if(!map.containsKey(name2)){
                NameData data = new NameData();
                data.setName1(name1);
                data.setName2(name2);
                data.setNameCode(nameCode);
                data.setNameValue(nameValue);
                map.put(name2, data);
                list.add(data);
            }else{
                NameData data = map.get(name2);
                String value = data.getNameValue() + "、" + nameValue;
                data.setNameValue(value);
            }
        }
        return list;
    }

    public static List<NameData> readSheet3() throws Exception {
        String filePath = "e:/tmp/2021.xlsx";
        File file = new File(filePath);
        Workbook workbook = WorkbookFactory.create(file);
        Sheet sheet = workbook.getSheetAt(3);
        int rowCount = sheet.getPhysicalNumberOfRows();
        List<NameData> list = new ArrayList<>();
        Map<String, NameData> map = new HashMap<>();
        for(int i=1; i<rowCount; i++){
            Row row = sheet.getRow(i);
            String nameCode = row.getCell(0).getStringCellValue();
            String name1 = row.getCell(2).getStringCellValue();
            String name2 = row.getCell(3).getStringCellValue();
            String nameValue = row.getCell(7).getStringCellValue();
            if(!map.containsKey(name2)){
                NameData data = new NameData();
                data.setName1(name1);
                data.setName2(name2);
                data.setNameCode(nameCode);
                data.setNameValue(nameValue);
                map.put(name2, data);
                list.add(data);
            }else{
                NameData data = map.get(name2);
                String value = data.getNameValue() + "、" + nameValue;
                data.setNameValue(value);
            }
        }
        return list;
    }

    public static void createSheet(XSSFSheet sheet, List<NameData> list) throws Exception{

        XSSFRow rowTitle = sheet.createRow(0);
        XSSFCell cellTitle0 = rowTitle.createCell(0);
        cellTitle0.setCellValue("院校代码");
        XSSFCell cellTitle1 = rowTitle.createCell(1);
        cellTitle1.setCellValue("院校名称");
        XSSFCell cellTitle2 = rowTitle.createCell(2);
        cellTitle2.setCellValue("院校名称");
        XSSFCell cellTitle3 = rowTitle.createCell(3);
        cellTitle3.setCellValue("专业名称");
        for(int i=0; i<list.size(); i++){
            NameData data = list.get(i);
            XSSFRow row = sheet.createRow(i + 1);
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(data.getNameCode());
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(data.getName1());
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(data.getName2());
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(data.getNameValue());
        }
    }


}
